package Dao.Role.Imp;

import Dao.Role.StuInfoDao;
import Entity.Role.Student;
import Util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StuInfoDaoImp implements StuInfoDao {
    private PreparedStatement ps = null;
    private ResultSet rs = null;

    @Override
    public List<Student> getStuByNO(String NO, Connection conn) {
        List<Student> students = new ArrayList<>();
        try {
            String sql = "select sno,password,name,sex,tel,email,collname,classes,role,bh,major_name ,tno ,email_code " +
                    "from stu_info where sno = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, NO);
            rs = ps.executeQuery();
            while (rs.next()) {
                String sNO = rs.getString("sno");
                String password = rs.getString("password");
                String name = rs.getString("name");
                String sex = rs.getString("sex");
                String tel = rs.getString("tel");
                String email = rs.getString("email");
                String collName = rs.getString("collname");
                String classes = rs.getString("classes");
                String role = rs.getString("role");
                String bh = rs.getString("bh");
                String majorName = rs.getString("major_name");
                String tno = rs.getString("tno");
                String emailCode = rs.getString("email_code");
                Student student = new Student(sNO, password, name, sex, tel, email, collName, classes, role, bh, majorName, tno, emailCode);
                students.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return students;
    }

    @Override
    public Integer updatePassBySNO(String SNO, String newPass, Connection conn) {
        int res = 0;
        try {
            String sql = "update stu_info set password = ? where sno = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, newPass);
            ps.setString(2, SNO);
            res = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return res;
    }

    @Override
    public Integer getSumByTNO(String TNO, Connection conn) {
        int res = 0;
        try {
            String sql = "select COUNT(sno) from stu_info  where tno = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, TNO);
            rs = ps.executeQuery();
            if (rs.next()) {
                res = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return res;
    }

    @Override
    public Integer updateEmailByNO(String NO, String newEmail, Connection conn) {
        int res = 0;
        try {
            String sql = "update stu_info set email = ? where sno = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, newEmail);
            ps.setString(2, NO);
            res = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return res;
    }

    @Override
    public Integer updateEmailCodeByNO(String NO, String emailCode, Connection conn) {
        int res = 0;
        try {
            String sql = "update stu_info set email_code = ? where sno = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, emailCode);
            ps.setString(2, NO);
            res = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return res;
    }

    @Override
    public Integer importImpStu(Student student, Connection conn) {
        int res = 0;
        try {
            String sql = "insert into stu_info (sno,password,name,sex,tel,email,collname,classes,major_name) " +
                    " values (?,?,?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, student.getSNO());
            ps.setString(2, student.getPassword());
            ps.setString(3, student.getName());
            ps.setString(4, student.getSex());
            ps.setString(5, student.getTel());
            ps.setString(6, student.getEmail());
            ps.setString(7, student.getCollName());
            ps.setString(8, student.getClasses());
            ps.setString(9, student.getMajorName());
            res = ps.executeUpdate();
        } catch (SQLException e) {
            String sql1 = "update stu_info " +
                    "   set password = ?,name = ?,sex = ?,tel = ?,email = ?,collname = ?,classes = ?,major_name =? " +
                    "  where sno = ?";
            try {
                ps = conn.prepareStatement(sql1);
                ps.setString(1, student.getPassword());
                ps.setString(2, student.getName());
                ps.setString(3, student.getSex());
                ps.setString(4, student.getTel());
                ps.setString(5, student.getEmail());
                ps.setString(6, student.getCollName());
                ps.setString(7, student.getClasses());
                ps.setString(8, student.getMajorName());
                ps.setString(9, student.getSNO());
                res = ps.executeUpdate();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return res;
    }
}
